/*
 * Created on 2005-3-31
 *
 */
package com.hd123.rumba.sql;

import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.List;

import com.hd123.rumba.request.Dictionary;
import com.hd123.rumba.request.JspLib;
import com.hd123.rumba.runtime.Manager;
import com.hd123.rumba.util.StringUtil;
import com.wutka.jox.JOXBeanInputStream;

/**
 * @author caili
 */
public class QueryMgr extends Manager {

  public QueryDefinition loadQueryDefinition(String resourceName) throws IOException {
    getLogger().debug("loading query definition from " + resourceName);
    InputStream in = getClass().getResourceAsStream(resourceName);
    QueryDefinition def = loadQueryDefinition(in);
    in.close();
    return def;
  }

  public QueryDefinition loadQueryDefinition(InputStream in) throws IOException {
    JOXBeanInputStream joxIn = new JOXBeanInputStream(in);
    QueryDefinition def = (QueryDefinition) joxIn.readObject(QueryDefinition.class);
    joxIn.close();
    return def;
  }

  /**
   * @param params:
   *          条件参数名称=条件值, 条件值可以是空.
   * @return
   */
  public SQLSubquery buildSQLSubquery(QueryDefinition def, Dictionary dd) {
    SQLSubquery sq = new SQLSubquery();
    int i;
    for (i = 0; i < def.getSelect().length; ++i) {
      sq.addSelect(def.getSelect()[i].getName(), def.getSelect()[i].getAlias());
    }
    for (i = 0; i < def.getFrom().length; ++i) {
      sq.addFrom(def.getFrom()[i].getName(), def.getFrom()[i].getAlias());
    }
    for (i = 0; def.getWhere() != null && i < def.getWhere().length; ++i) {
      Creteria where = def.getWhere()[i];
      if (where.getName() == null)
        sq.addWhere(where.getExpression());
      else if (dd != null) {
        Object value = dd.getValidFormValues().get(where.getName());
        // 如果是下拉列表, 取得其中表示"全部"的value: option.isIgnore() == true
        if (where.getOption() != null) {
          String ignoreValue = null;
          for (int j = 0; j < where.getOption().length; ++j) {
            Option option = where.getOption()[j];
            if (option.isIgnore()) {
              ignoreValue = option.getValue();
              break;
            }
          }
          // 只有在参数值不等于表示"全部"的value时才作为条件
          if (value != null && !value.equals(ignoreValue))
            sq.addWhere(where.getExpression(), value);
        } else if (value != null) {
          //if (!(value instanceof String) || ((String)
          // value).trim().length()
          // > 0)
          sq.addWhere(where.getExpression(), value, where.getWildcard());
        }
      }
    }

    Creteria co = def.getCustomOrder();
    if (dd != null && co != null) {
      String field = (String) dd.getFormValue(co.getName() + "Field");
      String order = (String) dd.getFormValue(co.getName() + "Order");
      // 如果是option提供的组合排序, 则忽略次序值
      boolean found = false;
      for (i = 0; co.getOption() != null && i < co.getOption().length; ++i) {
        if (co.getOption()[i].getValue().equals(field)) {
          found = true;
          break;
        }
      }
      if (found)
        sq.addOrder(field);
      else
        sq.addOrder(field, order);
    } else if (def.getOrder() != null) {
      sq.addOrder(def.getOrder().getName(), def.getOrder().getOrder());
    }
    return sq;
  }

  public String buildResultHtml(QueryDefinition def, List list) throws Exception {
    return buildResultHtml(def, list.iterator());
  }

  public String buildResultHtml(QueryDefinition def, Iterator iterator) throws Exception {
    StringBuffer sb = new StringBuffer();
    if (!iterator.hasNext()) {
      sb.append("<br>没有满足条件的结果.");
      return sb.toString();
    }
    boolean is1st = true;
    sb.append(dataTableStart);
    sb.append(headLineStart);
    if (def.getCheckbox() != null) {
      sb.append(head1stColumnStart);
      sb.append("<div id=\"A\">");
      sb.append("<a href=\"javascript:selectAll('form');\" class=\"tableheader\">全选</a>");
      sb.append("</div>");
      sb.append("<div id=\"B\" style=\"display:none\">");
      sb.append("<a href=\"javascript:selectAll('form');\" class=\"tableheader\">全否</a>");
      sb.append("</div>");
      sb.append(headColumnEnd);
      is1st = false;
    }
    for (int i = 0; i < def.getSelect().length; ++i) {
      Field f = def.getSelect()[i];
      if (f.isVisible()) {
        if (is1st) {
          is1st = false;
          sb.append(head1stColumnStart);
        } else {
          sb.append(headColumnStart);
        }
        sb.append(f.getLabel() == null ? f.getAlias() : f.getLabel());
        sb.append(headColumnEnd);
      }
    }

    sb.append(headLineEnd);
    for (int i = 0; iterator.hasNext(); ++i) {
      Object record = iterator.next();
      JdbcRow row;
      if (record instanceof JdbcRow)
        row = (JdbcRow) record;
      else
        row = new JdbcRow(record);
      sb.append(dataLineStart);
      is1st = true;
      if (def.getCheckbox() != null) {
        sb.append(data1stColumnStart);
        sb.append("<input type=checkbox name=\"");
        sb.append(def.getCheckbox().getName());
        sb.append("\" value=\"");
        String fieldName = def.getCheckbox().getValue();
        sb.append(row.get(fieldName));
        sb.append("\" />");
        sb.append(dataColumnEnd);
        is1st = false;
      }
      for (int j = 0; j < def.getSelect().length; ++j) {
        Field f = def.getSelect()[j];
        if (f.isVisible()) {
          if (is1st) {
            is1st = false;
            sb.append(data1stColumnStart);
          } else {
            sb.append(dataColumnStart);
          }
          String fieldname = f.getAlias();
          String fieldvalue = JspLib.format(row.get(fieldname));
          if (fieldvalue.trim().length() == 0)
            fieldvalue = "&nbsp;";
          else
            fieldvalue = StringUtil.stringToHtml(fieldvalue);
          if (f.getHref() != null) {
            sb.append("<a href=\"" + JspLib.getAction(f.getHref().getAddress()));
            if (f.getHref().getParameter().length > 0) {
              sb.append("?");
              for (int k = 0; k < f.getHref().getParameter().length; ++k) {
                if (k > 0)
                  sb.append("&");
                Field param = (Field) f.getHref().getParameter()[k];
                sb.append(param.getName());
                sb.append("=");
                if (param.getValue().equals("_alias_")) {
                  String fieldName = param.getAlias();
                  sb.append(row.get(fieldName));
                } else {
                  sb.append(param.getValue());
                }
              }
            }
            sb.append("\">");
          }
          sb.append(fieldvalue);
          if (f.getHref() != null) {
            sb.append("</a>");
          }
          sb.append(dataColumnEnd);
        }
      }
      sb.append(dataLineEnd);
    }
    sb.append(dataTableEnd);

    return sb.toString();
  }

  /**
   * 在数据字典(Dicitionary values)中生成resultTable和pageInfo jsp页面可以使用 <br>
   * &lt;%=dd.get("resultTable")%&gt; <br>
   * &lt;jsp:include
   * page="&lt;%=JspLib.getTemplate("rumba/inc/PagingBar.jsp")%&gt;"/&gt; <br>
   * &lt;jsp:include page="&lt;%=JspLib.getTemplate("inc/SelectAll.js")%&gt;"
   * /&gt; <br>
   * 显示
   * 
   * @param def
   *          查询定义
   * @param dd
   *          数据自动
   * @param pageSize
   *          页尺寸
   * @param page
   *          页码(0开始)
   * @return html代码
   * @throws Exception
   */
  public String buildResultHtml(QueryDefinition def, Dictionary dd, int pageSize, int page) throws Exception {
    SQLSubquery sq = buildSQLSubquery(def, dd);
    ResultPage rp = null;
    if ("hibernate".equals(def.getType()))
      rp = ResultPage.createHibernateResultPage(getSession(), sq, pageSize, page);
    else
      rp = ResultPage.createJdbcResultPage(getSession().connection(), sq, pageSize, page);
    Iterator iterator = rp.getContentIterator();
    String result = buildResultHtml(def, iterator);
    dd.put("resultTable", result);
    dd.put("pageInfo", rp);
    return result;
  }

  /**
   * 在数据字典(dicitionary value)中生成conditionTable. jsp页面中可以使用 <br>
   * &lt;%=dd.get("conditionTable")%&gt; <br>
   * 显示
   * 
   * @param def
   *          查询定义
   * @param columnCount
   *          每行列数
   * @param dd
   *          数据字典
   * @return html代码
   * @throws Exception
   */
  public String buildConditionHtml(QueryDefinition def, int columnCount, Dictionary dd) throws Exception {
    StringBuffer sb = new StringBuffer();
    sb.append(condTableStart);
    int col = 0;

    for (int i = 0; def.getWhere() != null && i < def.getWhere().length; ++i) {
      Creteria c = def.getWhere()[i];
      if (c.getLabel() != null) {
        if (col == 0) {
          sb.append(condLineStart);
        }
        if ((dd != null)
            && (dd.getInvalidFormValues() != null)
            && (dd.getInvalidFormValues().containsKey(c.getName() + "_" + Dictionary.INVALID) || dd
                .getInvalidFormValues().containsKey(c.getName() + "_" + Dictionary.MISSING)))
          sb.append(condErrorLabelStart);
        else
          sb.append(condLabelStart);
        sb.append(c.getLabel());
        sb.append(condLabelEnd);
        sb.append(condColumnStart);
        if (c.getOption() == null) {
          // 显示成input. 从params中获取默认值. 如果没有, 再从定义中获取.
          String defaultValue = (String) dd.getFormValue(c.getName());
          if (defaultValue == null)
            defaultValue = c.getDefaultValue();
          sb.append("<input name=\"" + c.getName() + "\"");
          if (defaultValue != null)
            sb.append(" value=\"" + defaultValue);
          sb.append("\">");
        } else {
          // 有预定义的条件值, 显示成下拉框. 从params中获取选中的项目. 如果没有, 再从定义中获取.
          String defaultValue = (String) dd.getFormValue(c.getName());
          if (defaultValue == null) {
            for (int j = 0; j < c.getOption().length; ++j) {
              Option option = c.getOption()[j];
              if (option.isSelected()) {
                defaultValue = option.getValue();
                break;
              }
            }
          }
          sb.append("<select name=\"" + c.getName() + "\">");
          for (int j = 0; j < c.getOption().length; ++j) {
            Option option = c.getOption()[j];
            sb.append("<option value=\"");
            sb.append(option.getValue());
            sb.append("\"");
            if (option.getValue().equals(defaultValue))
              sb.append(" selected=\"selected\"");
            sb.append(">");
            sb.append(option.getLabel());
            sb.append("</option>");
          }
          sb.append("</select>");
        }
        sb.append(condColumnEnd);
        col = (col + 1) % columnCount;
        if (col == 0) {
          sb.append(condLineEnd);
        }
      }
    }

    // 排序条件
    Creteria co = def.getCustomOrder();
    if (co != null) {
      // 找到默认的条件值. 先从dd中找, 没有的话再取def.getOrder().getName()
      String defaultField = (String) dd.getFormValue(co.getName() + "Field");
      if (defaultField == null && def.getOrder() != null) {
        defaultField = def.getOrder().getName();
      }
      String defaultOrder = (String) dd.getFormValue(co.getName() + "Order");
      if (defaultOrder == null && def.getOrder() != null) {
        defaultOrder = def.getOrder().getOrder();
      }

      if (col == 0) {
        sb.append(condLineStart);
      }
      sb.append(condColumnStart);
      // 添加所有字段为排序条件
      sb.append(co.getLabel());
      sb.append("<select name=\"");
      sb.append(co.getName() + "Field");
      sb.append("\">");
      for (int i = 0; i < def.getSelect().length; ++i) {
        Field f = def.getSelect()[i];
        if (!f.isVisible())
          continue;
        sb.append("<option value=\"");
        sb.append(f.getAlias());
        sb.append("\"");
        if (f.getAlias().equals(defaultField))
          sb.append(" selected=\"selected\"");
        sb.append(">");
        sb.append(f.getLabel());
        sb.append("</optioin>");
      }
      // 添加附加排序
      for (int i = 0; co.getOption() != null && i < co.getOption().length; ++i) {
        sb.append("<option value=\"");
        sb.append(co.getOption()[i].getValue());
        sb.append("\"");
        if (co.getOption()[i].getValue().equals(defaultField))
          sb.append(" selected=\"selected\"");
        sb.append(">");
        sb.append(co.getOption()[i].getLabel());
        sb.append("</optioin>");
      }
      sb.append("</select>");

      // 次序
      sb.append("<select name=\"");
      sb.append(co.getName() + "Order");
      sb.append("\">");
      sb.append("<option value=\"asc\"");
      if ("asc".equalsIgnoreCase(defaultOrder))
        sb.append(" selected=\"selected\"");
      sb.append(">");
      sb.append("升序</option>");
      sb.append("<option value=\"desc\"");
      if ("desc".equalsIgnoreCase(defaultOrder))
        sb.append(" selected=\"selected\"");
      sb.append("降序</option>");
      sb.append("</select>");

      sb.append(condColumnEnd);

      col = (col + 1) % columnCount;
      if (col == 0) {
        sb.append(condLineEnd);
      }
    }

    if (col != 0)
      sb.append(condLineEnd);
    sb.append(condTableEnd);
    dd.put("conditionTable", sb.toString());
    return sb.toString();
  }

  public boolean verifyConditions(QueryDefinition def, Dictionary dd) {
    for (int i = 0; i < def.getWhere().length; ++i) {
      Creteria c = def.getWhere()[i];
      String name = c.getName();
      if (name != null)
        dd.validateFormValue(name, c.getType(), c.isRequired());
      if (dd.getInvalidFormValues().containsKey(name + "_" + Dictionary.MISSING))
        dd.addErrorText("条件\"" + c.getLabel() + "\"值必须输入.");
      if (dd.getInvalidFormValues().containsKey(name + "_" + Dictionary.INVALID)) {
        if (c.getErrorMessage() != null)
          dd.addErrorText(c.getErrorMessage());
        else
          dd.addErrorText("条件\"" + c.getLabel() + "\"值不正确. 正确值的格式为" + c.getType());
      }
    }
    return dd.isFormValid();
  }

  static final String dataTableStart = "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\" class=\"n\">";
  static final String dataTableEnd = "</table>";
  static final String headLineStart = "<tr>";
  static final String headLineEnd = "</tr>";
  static final String head1stColumnStart = "<td class=\"table_header w e s center\" nowrap=\"nowrap\">";
  static final String headColumnStart = "<td class=\"table_header e s center\" nowrap=\"nowrap\">";
  static final String headColumnEnd = "</td>";
  static final String dataLineStart = "<tr>";
  static final String dataLineEnd = "</tr>";
  static final String data1stColumnStart = "<td class=\"table_detail w e s\">";
  static final String dataColumnStart = "<td class=\"table_detail e s\">";
  static final String dataColumnEnd = "</td>";

  static final String condTableStart = "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\" class=\"n e s w\">";
  static final String condTableEnd = "</table>";
  static final String condLineStart = "<tr>";
  static final String condLineEnd = "</tr>";
  static final String condLabelStart = "<td class=\"table_detail\" align=right>";
  static final String condErrorLabelStart = "<td class=\"fielditem2_error\" align=right>";
  static final String condLabelEnd = "</td>";
  static final String condColumnStart = "<td class=\"table_detail\">";
  static final String condColumnEnd = "</td>";
}
